package com.bsoft.gol.hcs.specialist.dao;

import com.bsoft.gol.hcs.specialist.bean.qo.ComplaintStatisticsRequest;
import com.bsoft.gol.hcs.specialist.bean.qo.FindComplaintListQo;
import com.bsoft.gol.hcs.specialist.bean.vo.FindComplaintListVo;
import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.HibernateSupportDelegateDAO;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import hcn.specialist.BaseComplaint;
import hcn.util.CommonUtils;
import org.atag.dao.action.QoHibernateStatelessResultAction;
import org.atag.dao.model.PageInfo;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author zsf
 * @Date 2023/10/19 0019 9:30
 * @PackageName:com.bsoft.gol.hcs.specialist.dao
 * @ClassName: BaseComplaintDAO
 * @Description: 投诉主表dao
 * @Version 4.0.02
 */
public abstract class BaseComplaintDAO extends HibernateSupportDelegateDAO<BaseComplaint> {
    public BaseComplaintDAO() {
        this.setKeyField("complaintId");
    }


    @DAOMethod(sql = " from BaseComplaint where openId =:openId order by createTime desc ")
    public abstract List<BaseComplaint> findComplaintList(@DAOParam("openId") String openId, @DAOParam(pageStart = true) int start, @DAOParam(pageLimit = true) int limit);

    @DAOMethod(sql = " select count(1) from BaseComplaint where openId =:openId ")
    public abstract Long getComplaintListTotal(@DAOParam("openId") String openId);

    /**
     * @param
     * @return java.util.List<hcn.specialist.BaseComplaint>
     * @description: 测试
     * @author: zsf
     * @date: 2023/10/24 0024 10:29
     */
    @DAOMethod(sql = " update BaseComplaint set dealQuality =:dealQuality,status =:status where complaintId=:complaintId ")
    public abstract void updateDealQuality(@DAOParam("complaintId") String complaintId, @DAOParam("dealQuality") Byte dealQualit, @DAOParam("status") Integer status);

    /**
     * @param complaintId
     * @param rectQuality
     * @return void
     * @description: 整改质量审核
     * @author: zsf
     * @date: 2023/10/26 0026 13:26
     */
    @DAOMethod(sql = " update BaseComplaint set rectQuality =:rectQuality,status =:status where complaintId=:complaintId ")
    public abstract void updateRectQuality(@DAOParam("complaintId") String complaintId, @DAOParam("rectQuality") Byte rectQuality, @DAOParam("status") Integer status);

    /**
     * @param complaintId
     * @param rectQuality
     * @return void
     * @description: 整改质量审核
     * @author: zsf
     * @date: 2023/10/26 0026 13:26
     */
    @DAOMethod(sql = " update BaseComplaint set status =:status where complaintId=:complaintId ")
    public abstract void updateStatus(@DAOParam("complaintId") String complaintId, @DAOParam("status") Integer status);


    @DAOMethod(sql = " update BaseComplaint set evaluateResult =:evaluateResult,status =:status where complaintId=:complaintId ")
    public abstract void updateEvaluateQuality(@DAOParam("complaintId") String complaintId, @DAOParam("evaluateResult") Byte evaluateResult, @DAOParam("status") Integer status);

    /**
     * @param qo
     * @return org.atag.dao.model.PageInfo<com.bsoft.gol.hcs.specialist.bean.vo.FindComplaintListVo>
     * @description: 查询整改列表
     * @author: zsf
     * @date: 2023/10/25 0025 17:45
     */
    public PageInfo<FindComplaintListVo> findComplaintListRect(FindComplaintListQo qo) {
        QoHibernateStatelessResultAction<FindComplaintListVo, FindComplaintListQo> action = new QoHibernateStatelessResultAction(true, qo, FindComplaintListVo.class);
        StringBuilder sql = new StringBuilder("SELECT  {{" +
                "  a.complaintant, " +
                "  a.complaint_id as complaintId, " +
                "  a.complaint_type as complaintType,  " +
                "  a.emergency_type as emergencyType,  " +
                "  a.event_property as eventProperty,  " +
                "  a.event_property_sencond as eventPropertySencond,  " +
                "  a.duty_organization_name as dutyOrganizationName,  " +
                "  b.dept_name as deptName,  " +
                "  b.dept_flag as deptFlag,  " +
                "  a.complaint_time as complaintTime,  " +
                "  a.feedback_content as feedbackContent,  " +
                "  a.deal_result as dealResult,  " +
                "  a.deal_quality as dealQuality,  " +
                "  a.rect_quality as rectQuality,  " +
                "  a.evaluate_result as evaluateResult, " +
                "  a.supervision_time as supervisionTime," +
                "  a.complaint_sort_first as complaintSortFirst, " +
                "  a.complaint_sort_first_id as complaintSortFirstId, " +
                "  a.rect_flag as rectFlag, " +
                "  b.sign_time  as signTime," +
                "  b.rect_id as rectId ," +
                "  a.status as status," +
                "  (select operat_name from base_complaint_trace c where a.complaint_id = c.complaint_id and c.status = 2 order by create_time desc limit 1  ) as operatName }} " +
                "FROM  " +
                "  base_complaint a  " +
                "  RIGHT JOIN base_complaint_rect b ON a.complaint_id = b.complaint_id where 1=1  ");
        //查询待整改记录的时候需要判断是不是部分整改,且未整改的-pc和医生端查询待整改的时候需要判断当前投诉状态,以及整改科室的整改状态
        List<Integer> status = new ArrayList<>();
        status.add(4);
        status.add(5);
        if (qo.getStatus().equals(status)) {
            sql.append(" and b.status = 0 ");
        }
        action.setSql(sql.toString());
        //责任机构-->先过滤团队权限
//        action.addCondition(" and a.duty_organization_id = :dutyOrganizationId");
        //反馈方式
        action.addCondition(" and a.complaint_type = :complaintType");
        //事件性质->待处理的取这个
        action.addCondition(" and a.event_property = :eventProperty");
        //事件性质->处理完之后的取这个
        action.addCondition(" and a.event_property_sencond = :eventPropertySencond");
        //事件类型
        action.addCondition(" and a.emergency_type = :emergencyType");
        //投诉类别
        action.addCondition(" and a.complaint_sort_first_id = :complaintSortFirstId");
        //处理结果
        action.addCondition(" and a.deal_result = :dealResult");
        //处理质量
        action.addCondition(" and a.deal_quality = :dealQuality");
        //整改质量
        action.addCondition(" and a.rect_quality = :rectQuality");
        //是否需要整改
        action.addCondition(" and a.rect_flag = :rectFlag");
        //整改质量
        action.addCondition(" and a.evaluate_result = :evaluateResult");
        //状态
        action.addCondition(" and a.status in (:status)");
        //内容
        action.addCondition(" and a.feedback_content like :%feedbackContent%");
        //投诉开始时间
        action.addCondition(" and a.complaint_time > :complaintBeginDate");
        //投诉结束时间
        action.addCondition(" and a.complaint_time < :complaintBeginDate");
        //评定时间
        action.addCondition(" and a.supervision_time > :supervisionBeginTime");
        //评定时间
        action.addCondition(" and a.supervision_time < :supervisionEndTime");
        //责任机构
        action.addCondition(" and a.duty_organization_id in (:organizationIdList)");
        //责任机构
        action.addCondition(" and b.dept_id in (:deptIdList)");

        action.addSql(" ORDER BY complaint_time desc ");
        HibernateSessionTemplate.instance().execute(action);
        PageInfo<FindComplaintListVo> pageInfo = action.getResultPage();
        return pageInfo;
    }

    /**
     * @param qo
     * @return org.atag.dao.model.PageInfo<com.bsoft.gol.hcs.specialist.bean.vo.FindComplaintListVo>
     * @description: 查询投诉列表
     * @author: zsf
     * @date: 2023/10/25 0025 17:45
     */
    public PageInfo<FindComplaintListVo> findComplaintList(FindComplaintListQo qo) {
        QoHibernateStatelessResultAction<FindComplaintListVo, FindComplaintListQo> action = new QoHibernateStatelessResultAction(true, qo, FindComplaintListVo.class);
        action.setSql("SELECT  {{" +
                "  a.complaintant, " +
                "  a.complaint_id as complaintId, " +
                "  a.complaint_type as complaintType,  " +
                "  a.emergency_type as emergencyType,  " +
                "  a.event_property as eventProperty,  " +
                "  a.event_property_sencond as eventPropertySencond,  " +
                "  a.duty_organization_name as dutyOrganizationName,  " +
//                "  b.complaint_dept_name as deptName,  " +
//                "  b.dept_flag as deptFlag,  " +
                "  a.complaint_time as complaintTime,  " +
                "  a.feedback_content as feedbackContent,  " +
                "  a.deal_result as dealResult,  " +
                "  a.deal_quality as dealQuality,  " +
                "  a.rect_quality as rectQuality,  " +
                "  a.evaluate_result as evaluateResult, " +
                "  a.supervision_time as supervisionTime," +
                "  a.complaint_sort_first as complaintSortFirst, " +
                "  a.complaint_sort_first_id as complaintSortFirstId, " +
                "  a.rect_flag as rectFlag, " +
//                "  b.sign_time  as signTime," +
//                "  b.rect_id as rectId ," +
                "  a.status as status," +
                "  (select operat_name from base_complaint_trace c where a.complaint_id = c.complaint_id and c.status = 2 order by create_time desc limit 1  ) as operatName }} " +
                "FROM  " +
                "  base_complaint a  " +
                "  where 1=1  ");
        //责任机构-->这个机构权限要先过滤团队权限
//        action.addCondition(" and a.duty_organization_id = :dutyOrganizationId");
        //反馈方式
        action.addCondition(" and a.complaint_type = :complaintType");
        //事件性质->待处理的取这个
        action.addCondition(" and a.event_property = :eventProperty");
        //事件性质->处理完之后的取这个
        action.addCondition(" and a.event_property_sencond = :eventPropertySencond");
        //事件类型
        action.addCondition(" and a.emergency_type = :emergencyType");
        //投诉类别
        action.addCondition(" and a.complaint_sort_first_id = :complaintSortFirstId");
        //处理结果
        action.addCondition(" and a.deal_result = :dealResult");
        //处理质量
        action.addCondition(" and a.deal_quality = :dealQuality");
        //整改质量
        action.addCondition(" and a.rect_quality = :rectQuality");
        //整改质量
        action.addCondition(" and a.rect_flag = :rectFlag");
        //整改质量
        action.addCondition(" and a.evaluate_result = :evaluateResult");
        //状态
        action.addCondition(" and a.status in (:status)");
        //内容
        action.addCondition(" and a.feedback_content like :%feedbackContent%");
        //投诉开始时间
        action.addCondition(" and a.complaint_time > :complaintBeginDate");
        //投诉结束时间
        action.addCondition(" and a.complaint_time < :complaintEndDate");
        //评定时间
        action.addCondition(" and a.supervision_time > :supervisionBeginTime");
        //评定时间
        action.addCondition(" and a.supervision_time < :supervisionEndTime");
        //责任机构
        action.addCondition(" and a.duty_organization_id in (:organizationIdList)");

        action.addSql(" ORDER BY complaint_time desc ");
        HibernateSessionTemplate.instance().execute(action);
        PageInfo<FindComplaintListVo> pageInfo = action.getResultPage();
        return pageInfo;
    }


    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 科室汇总分析
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public Map<String, HashMap<String, Object>> complaintDeptStatistics(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<Map<String, HashMap<String, Object>>> action = new AbstractHibernateStatelessResultAction<Map<String, HashMap<String, Object>>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" select complaint_dept_id deptId,complaint_dept_name deptName ,dept_id_first as deptIdFirst,dept_name_first as deptNameFirst, DATE_FORMAT(create_time,'%Y-%m') month ,count(1) count " +
                        " from base_complaint where emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintYear())) {
                    sql.append(" and DATE_FORMAT(create_time,'%Y') =:complaintYear ");
                }
                sql.append("  GROUP BY  dept_id_first,complaint_dept_id,month ORDER BY complaint_dept_id,month ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintYear())) {
                    query.setParameter("complaintYear", request.getComplaintYear());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);


                Map<String, HashMap<String, Object>> data = new HashMap<>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    list.stream().forEach(l -> {
                        HashMap<String, Object> map = data.get(l.get("deptId"));
                        if (map == null) {
                            map = new HashMap<>();
                        }
                        map.put((String) l.get("month"), (BigInteger) l.get("count"));
                        data.put((String) l.get("deptIdFirst")+(String) l.get("deptId"), map);
                    });
                    setResult(data);
                }
                setResult(data);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }



    @DAOMethod(sql = " select count(1) from  BaseComplaint where  emergency_type = 0 and ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8))  and DATE_FORMAT( create_time, '%Y' ) =:complaintYear")
    public abstract long getComplaintDeptIdList(@DAOParam("complaintYear") String complaintYear);

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 查询科室列表
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> findComplaintDeptIdList(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" select distinct complaint_dept_id as complaintDeptId,complaint_dept_name as complaintDeptName " +
                        ",dept_id_first as deptIdFirst,dept_name_first as deptNameFirst from " +
                        " base_complaint where emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintYear())) {
                    sql.append(" and  DATE_FORMAT( create_time, '%Y' ) =:complaintYear ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append(" GROUP BY  dept_id_first,complaint_dept_id ");
                Query query = ss.createSQLQuery(sql.toString());
                if (CommonUtils.isNotEmpty(request.getComplaintYear())) {
                    query.setParameter("complaintYear", request.getComplaintYear());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份分类的总数-查询第一大类的分类1
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsByMonth(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        " DATE_FORMAT( create_time, '%Y-%m' ) month," +
                        "complaint_sort_first_id AS complaintSortFirstId " +
                        "FROM" +
                        " base_complaint where emergency_type = 0 and ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY month,complaint_sort_first_id  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份分类的总数-查询第一大类的分类1
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsGroupMonth(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        " DATE_FORMAT( create_time, '%Y-%m' ) month " +
                        " FROM " +
                        " base_complaint where emergency_type = 0 and ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8))  ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY month  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份分类的总数-查询第一大类的分类1
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsGroupDept(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaint_dept_id as complaintDeptId," +
                        "complaint_dept_name as complaintDeptName," +
                        "dept_id_first as deptIdFirst," +
                        "dept_name_first as deptNameFirst " +
                        " FROM " +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY dept_id_first,complaint_dept_id  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

 /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份分类的总数-查询第一大类的分类1
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsOrgCount(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total " +
                        "FROM" +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份1分类的总数-查询第二大类的分类
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsMonthSecond(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        " DATE_FORMAT( create_time, '%Y-%m' ) month," +
                        "complaint_sort_second_id AS complaintSortSecondId " +
                        "FROM" +
                        " base_complaint where  emergency_type = 0 and  complaint_sort_first_id = 3 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY month,complaint_sort_second_id  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询投诉人意见
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsMonthPoint(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        " DATE_FORMAT( create_time, '%Y-%m' ) month," +
                        "complaintant_point AS complaintantPoint " +
                        " FROM " +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY month,complaintant_point  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份分类的总数-查询第一大类的分类1
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsByDept(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaint_sort_first_id AS complaintSortFirstId ," +
                        "complaint_dept_id as complaintDeptId," +
                        "complaint_dept_name as complaintDeptName," +
                        "dept_id_first as deptIdFirst," +
                        "dept_name_first as deptNameFirst " +
                        "FROM" +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8))  ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY dept_id_first,complaint_dept_id,complaint_sort_first_id  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个月份1分类的总数-查询第二大类的分类
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsDeptSecond(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaint_sort_second_id AS complaintSortSecondId, " +
                        "complaint_dept_id as complaintDeptId," +
                        "complaint_dept_name as complaintDeptName," +
                        "dept_id_first as deptIdFirst," +
                        "dept_name_first as deptNameFirst " +
                        "FROM" +
                        " base_complaint where  emergency_type = 0 and  complaint_sort_first_id = 3 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY  dept_id_first,complaint_dept_id,complaint_sort_second_id  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询投诉人意见
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsDeptPoint(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaintant_point AS complaintantPoint, " +
                        "complaint_dept_id as complaintDeptId," +
                        "complaint_dept_name as complaintDeptName," +
                        "dept_id_first as deptIdFirst," +
                        "dept_name_first as deptNameFirst " +
                        "FROM" +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY  dept_id_first,complaint_dept_id,complaintant_point  ");

                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个分类的总数-查询第一大类的分类1
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsMonthOrgByMonth(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaint_sort_first_id AS complaintSortFirstId " +
                        " FROM " +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY complaint_sort_first_id  ");
                //按照月份查询不需要区分机构
                if (request.getComplaintObject() == 2) {
                    sql.append(",duty_organization_id ");
                }
                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析-查询机构下的各个分类的总数-查询第一大类的分类2
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsMonthOrgSecond(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaint_sort_second_id AS complaintSortSecondId " +
                        " FROM " +
                        " base_complaint where  emergency_type = 0 and  complaint_sort_first_id = 3 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY complaint_sort_second_id  ");
                //按照月份查询不需要区分机构
                if (request.getComplaintObject() == 2) {
                    sql.append(",duty_organization_id ");
                }
                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @return java.util.Map<java.lang.String, java.util.Map < java.lang.String, java.lang.Object>>
     * @description: 投诉汇总分析, 按照月份查询-查询机构下的各个分类的总数-查询第一大类的分类2
     * @author: zsf
     * @date: 2023/11/5 0005 10:17
     */
    public List<Map> complaintStatisticsMonthOrgPoint(final ComplaintStatisticsRequest request) {
        HibernateStatelessResultAction<List<Map>> action = new AbstractHibernateStatelessResultAction<List<Map>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" SELECT" +
                        " count( 1 ) as total ," +
                        "complaintant_point AS complaintantPoint " +
                        " FROM " +
                        " base_complaint where  emergency_type = 0 and  ((rect_flag = 1 and status >=4 )  or (rect_flag = 0 and status >= 8)) ");
                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    sql.append(" and create_time>=:complaintBeginTime ");
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    sql.append("and create_time<=:complaintEndTime ");
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    sql.append(" and duty_organization_id=:dutyOrganizationId ");
                }
                sql.append("  GROUP BY complaintant_point  ");
                //按照月份查询不需要区分机构
                if (request.getComplaintObject()==2){
                    sql.append(",duty_organization_id ");
                }
                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(request.getComplaintBeginTime())) {
                    query.setParameter("complaintBeginTime", request.getComplaintBeginTime());
                }
                if (CommonUtils.isNotEmpty(request.getComplaintEndTime())) {
                    query.setParameter("complaintEndTime", request.getComplaintEndTime());
                }
                if (CommonUtils.isNotEmpty(request.getDutyOrganizationId())) {
                    query.setParameter("dutyOrganizationId", request.getDutyOrganizationId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(list);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

}
